package com.naiyumi.common;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import javax.faces.model.SelectItem;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.naiyumi.util.DateUtil;

/**
 * 
 * @author 王洋
 * @company 中国网通系统集成有限公司山分BSS
 * @date Mar 11, 2008
 * 
 */
@Repository
@SuppressWarnings("serial")
public class DBConnect implements Serializable {

	private Logger logger = Logger.getLogger(DBConnect.class.getName());
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public int execUpdate(String sql) {
		logger.info("execUpdate[SQL]=" + sql);
		return jdbcTemplate.update(sql);
	}

	@SuppressWarnings("unchecked")
	public List<Map> execQuery(String sql) {
		logger.info("execQuery[SQL]=" + sql);
		return (List)this.jdbcTemplate.queryForList(sql);
	}

	@SuppressWarnings("unchecked")
	public List<SelectItem> execQueryForSelect(String sql) {
		List<Map> rs = this.execQuery(sql);
		logger.info("execQueryForSelect[SQL]=" + sql);
		List<SelectItem> newRs = new ArrayList();
		SelectItem selectItem = null;
		String key = null, id = null, name = null;
		Iterator it = null;
		for (Map entry : rs) {
			it = entry.keySet().iterator();
			int i = 0;
			selectItem = new SelectItem();
			while (it.hasNext()) {
				key = it.next().toString();
				if (i == 0) {
					/* Add by 王加林, 2008-04-29, START */
					/*
					 * 数据库中数据为空时，则可能在取出并转换成字符串时出错 因此对数据进行判断
					 */
					Object tmp = entry.get(key);
					if (tmp == null) {
						id = "";
					} else {
						id = tmp.toString();
					}
					/* Add by 王加林, 2008-04-29, END */
					// id = entry.get(key).toString();
				}
				if (i == 1) {
					/* Add by 王加林, 2008-04-29, START */
					/*
					 * 数据库中数据为空时，则可能在取出并转换成字符串时出错 因此对数据进行判断
					 */
					Object tmp = entry.get(key);
					if (tmp == null) {
						name = "";
					} else {
						name = tmp.toString();
					}
					/* Add by 王加林, 2008-04-29, END */
					// name = entry.get(key).toString();
				}
				i++;
				if (i > 1) {
					selectItem.setLabel(name);
					selectItem.setValue(id);
					break;
				}
			}
			newRs.add(selectItem);
		}

		// if(newRs.size()<=0){
		// selectItem = new SelectItem();
		// selectItem.setLabel("");
		// selectItem.setValue("");
		// newRs.add(selectItem);
		// }

		return newRs;
	}

	@SuppressWarnings("unchecked")
	public DBResultset execQueryForSet(String sql) {
		logger.info("execQueryForSet[SQL]=" + sql);
		List rs = this.jdbcTemplate.queryForList(sql);
		DBResultset dBResultset = new DBResultset(rs);
		return dBResultset;
	}
	

	public int execQueryForInt(String sql) {
		logger.info("execQueryForInt[SQL]=" + sql);
		return this.jdbcTemplate.queryForInt(sql);
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@SuppressWarnings("unchecked")
	public DBResultset execQueryPage(String sql, int currentPage, int numPerPage) {
		logger.info("execQueryPage[SQL]=" + sql);
		/* 设置起始行 */
		int startRow = (currentPage - 1) * numPerPage + 1;

		/* 设置终止行 */
		int endRow = currentPage * numPerPage;
		/* 从数据库查询结果集 */
		StringBuffer mysql = new StringBuffer(200);
		mysql.append("select * from (").append("select * from (").append(
				"select A.*,rownum r from");
		mysql.append(" (").append(sql).append(") ").append("A").append(
				") where rownum<=").append(endRow);
		mysql.append(") B").append(" where r>=").append(startRow);
		List rs = this.execQuery(mysql.toString());
		DBResultset dBResultset = new DBResultset(rs);
		return dBResultset;
	}

	@SuppressWarnings("unchecked")
	public List<Map> execQueryPageByList(String sql, int currentPage, int pageSize) {
		/* 设置起始行 */
		int startRow = (currentPage - 1) * pageSize + 1;

		/* 设置终止行 */
		int endRow = currentPage * pageSize;

		/* 从数据库查询结果集 */
		StringBuffer mysql = new StringBuffer(200);
		mysql.append("select * from (").append("select * from (").append(
				"select A.*,rownum r from");
		mysql.append(" (").append(sql).append(") ").append("A").append(
				") where rownum<=").append(endRow);
		mysql.append(") B").append(" where r>=").append(startRow);
		logger.info("execQueryPageByList[SQL]=" + mysql.toString());
		List<Map> rs = this.execQuery(mysql.toString());

		return rs;
	}

	/**
	 * 批量插入方法
	 * 
	 * @param sql
	 * @param values
	 * @return
	 */
	public int[] execBatchUpdate(String sql,
			final List<Map<String, String>> values) {
		BatchPreparedStatementSetter setter = null;
		setter = new BatchPreparedStatementSetter() {
			public int getBatchSize() {
//				logger.info(values.size());
				return values.size();
			}

			@SuppressWarnings("unchecked")
			public void setValues(PreparedStatement ps, int index)
					throws SQLException {
				Map<String, String> value = values.get(index);
				Iterator it = value.keySet().iterator();
				int parameterIndex = 1;
				while (it.hasNext()) {
					String type = it.next().toString();
					String v = value.get(type);
					logger.info(v);
					if (type.indexOf("Int") != -1) {
						ps.setInt(parameterIndex, new Integer(v));
					}
					if (type.indexOf("String") != -1) {
						ps.setString(parameterIndex, v);
					}
					if (type.indexOf("Float") != -1) {
						ps.setFloat(parameterIndex, new BigDecimal(v)
								.floatValue());
					}
					if(type.indexOf("Double")!=-1){//BY LUKE 2011.04.06加入double类型数值
						ps.setDouble(parameterIndex,new BigDecimal(v).
								doubleValue());
					}
					if (type.indexOf("Date1") != -1) {
						ps
								.setTimestamp(parameterIndex, DateUtil
										.string2Time(v));
					}
					if (type.indexOf("Date2") != -1) {
						ps.setDate(parameterIndex, DateUtil.parseSQLDate(v));
					}
					parameterIndex++;
				}
				// ps.addBatch();
			}
		};
		return jdbcTemplate.batchUpdate(sql, setter);
	}

	/**
	 * @Title: execQueryForPage_oscar方法
	 * @Description: oscar分页 
	 * @param sql
	 * @param currentPage
	 * @param pageSize
	 * @return 返回结果集
	 * @author:刘东升
	 */
	public DBResultset execQueryForPage_oscar(String sql, int currentPage, int pageSize){
		//设置分页 
		int startRow = pageSize * (currentPage - 1) ;
		StringBuffer mysql = new StringBuffer(200);
		mysql.append(sql);
		mysql.append(" limit ");
		mysql.append(pageSize);		
		mysql.append(" OFFSET ");
		mysql.append(startRow);
		return this.execQueryForSet(mysql.toString());
	}
}
